package com.nike.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class NikeDao{
	
	public List<Map<String,Object>> getAllZhanghaoLoginForTel(){
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		Statement stmt = null;
		Connection c = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			c = DriverManager
				.getConnection("jdbc:mysql://localhost:3306/nike?user=root&password=123456&useUnicode=true&characterEncoding=utf8");
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "select email,pwd,tel from zhanghao where registerstatus='1' and loginstatus='0' limit 1";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("email",rs.getString("email"));
				map.put("pwd",rs.getString("pwd"));
				list.add(map);
			}
			stmt.close();
			c.commit();
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}finally{
			if(c!=null)
				try {
					c.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return list;
	}
	
	public List<Map<String,Object>> getAllZhanghaoForRegister(){
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		Statement stmt = null;
		Connection c = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			c = DriverManager
				.getConnection("jdbc:mysql://localhost:3306/nike?user=root&password=123456&useUnicode=true&characterEncoding=utf8");
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "select email,pwd,lastname,firstname,tel from zhanghao where registerstatus='0'  order by email limit 1";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("email",rs.getString("email"));
				map.put("pwd",rs.getString("pwd"));
				map.put("lastname",rs.getString("lastname"));
				map.put("firstname",rs.getString("firstname"));
				map.put("tel",rs.getString("tel"));
				list.add(map);
			}
			stmt.close();
			c.commit();
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}finally{
			if(c!=null)
				try {
					c.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return list;
	}
	
	public Map<String,Object> getAllZhanghaoOne(Map<String,Object> emailmap){
		Statement stmt = null;
		Connection c = null;
		Map<String,Object> map = new HashMap<String,Object>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			c = DriverManager
				.getConnection("jdbc:mysql://localhost:3306/nike?user=root&password=123456&useUnicode=true&characterEncoding=utf8");
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "select email,pwd,lastname,firstname,tel from zhanghao  where email = '"+emailmap.get("email")+"'";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				
				map.put("email",rs.getString("email"));
				map.put("pwd",rs.getString("pwd"));
				map.put("lastname",rs.getString("lastname"));
				map.put("firstname",rs.getString("firstname"));
				map.put("tel",rs.getString("tel"));
			}
			stmt.close();
			c.commit();
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}finally{
			if(c!=null)
				try {
					c.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return map;
	}
	public List<Map<String,Object>> getAllZhanghaoNoTel(){
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		Statement stmt = null;
		Connection c = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			c = DriverManager
				.getConnection("jdbc:mysql://localhost:3306/nike?user=root&password=123456&useUnicode=true&characterEncoding=utf8");
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "select email,pwd,lastname,firstname,tel from zhanghao where registerstatus='1' and loginstatus='0'  order by email limit 1";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("email",rs.getString("email"));
				map.put("pwd",rs.getString("pwd"));
				map.put("lastname",rs.getString("lastname"));
				map.put("firstname",rs.getString("firstname"));
				map.put("tel",rs.getString("tel"));
				list.add(map);
			}
			stmt.close();
			c.commit();
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}finally{
			if(c!=null)
				try {
					c.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return list;
	}
	public List<Map<String,Object>> getAllZhanghaoLogin(){
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		Statement stmt = null;
		Connection c = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			c = DriverManager
				.getConnection("jdbc:mysql://localhost:3306/nike?user=root&password=123456&useUnicode=true&characterEncoding=utf8");
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "select email,pwd,lastname,firstname,tel from zhanghao where registerstatus='1' and loginstatus='0' limit 15";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("email",rs.getString("email"));
				map.put("pwd",rs.getString("pwd"));
				map.put("lastname",rs.getString("lastname"));
				map.put("firstname",rs.getString("firstname"));
				map.put("tel",rs.getString("tel"));
				list.add(map);
			}
			stmt.close();
			c.commit();
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}finally{
			if(c!=null)
				try {
					c.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return list;
	}
	public List<Map<String,Object>> getAllZhanghao(){
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		Statement stmt = null;
		Connection c = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			c = DriverManager
				.getConnection("jdbc:mysql://localhost:3306/nike?user=root&password=123456&useUnicode=true&characterEncoding=utf8");
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "select email,pwd from zhanghao where registerstatus='0' limit 20";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("email",rs.getString("email"));
				map.put("pwd",rs.getString("pwd"));
				list.add(map);
			}
			stmt.close();
			c.commit();
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}finally{
			if(c!=null)
				try {
					c.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return list;
	}
	public Map<String,Object> getZhanghaoNameOne(){
		Map<String,Object> map = new HashMap<String,Object>();
		Statement stmt = null;
		Connection c = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			c = DriverManager
				.getConnection("jdbc:mysql://localhost:3306/nike?user=root&password=123456&useUnicode=true&characterEncoding=utf8");
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "select name from zhanghaoname order by RAND() limit 1";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				map.put("name",rs.getString("name"));
			}
			stmt.close();
			c.commit();
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}finally{
			if(c!=null)
				try {
					c.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return map;
	}
	
	public int updateTelphone(Map<String,Object> map) {
		Statement stmt = null;
		Connection c = null;
		int return_int = 0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			c = DriverManager
				.getConnection("jdbc:mysql://localhost:3306/nike?user=root&password=123456&useUnicode=true&characterEncoding=utf8");
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "update zhanghao set tel='"+map.get("tel")+"' where email = '"+map.get("email")+"'";
			return_int = stmt.executeUpdate(sql);
			stmt.close();
			c.commit();
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}finally{
			if(c!=null)
				try {
					c.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return return_int;
	}
	public int updateLoginStatus(Map<String,Object> map) {
		Statement stmt = null;
		Connection c = null;
		int return_int = 0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			c = DriverManager
				.getConnection("jdbc:mysql://localhost:3306/nike?user=root&password=123456&useUnicode=true&characterEncoding=utf8");
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "update zhanghao set province='"+map.get("province")+"',city='"+map.get("city")+"',area='"+map.get("area")+"',address='"+map.get("address")+"',loginstatus='"+map.get("loginstatus")+"' where email = '"+map.get("email")+"'";
			return_int = stmt.executeUpdate(sql);
			stmt.close();
			c.commit();
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}finally{
			if(c!=null)
				try {
					c.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return return_int;
	}
	public int updateRegisterStatus(Map<String,Object> map) {
		Statement stmt = null;
		Connection c = null;
		int return_int = 0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			c = DriverManager
				.getConnection("jdbc:mysql://localhost:3306/nike?user=root&password=123456&useUnicode=true&characterEncoding=utf8");
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "update zhanghao set lastname='"+map.get("lastName")+"',firstname='"+map.get("firstName")+"',registerstatus='"+map.get("registerstatus")+"' where email = '"+map.get("email")+"'";
			return_int = stmt.executeUpdate(sql);
			stmt.close();
			c.commit();
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}finally{
			if(c!=null)
				try {
					c.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return return_int;
	}
}